USE AccessControl
/********************************************************

  COUNT OF EVENTS PER SECOND IN THE SPECIFIED TIMEFRAME

*********************************************************/
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
	DROP TABLE #Temp

SELECT  CAST(EVENT_TIME_UTC as DATE) as Event_Date, 
		DATEPART(HOUR,EVENT_TIME_UTC) as Hrs, 
		DATEPART(MINUTE, EVENT_TIME_UTC) as Mins,
		DATEPART(SECOND, EVENT_TIME_UTC) as Secs, 
		COUNT(SERIALNUM) as Total_Entries 
INTO #Temp
FROM [ACCESSCONTROL].[dbo].[EVENTS]
WHERE EVENT_TIME_UTC BETWEEN '2020-05-01 00:00:00' AND '2020-07-09 00:00:00'
GROUP BY CAST(EVENT_TIME_UTC AS DATE), 
		DATEPART(HOUR,EVENT_TIME_UTC), 
		DATEPART(MINUTE, EVENT_TIME_UTC),
		DATEPART(SECOND, EVENT_TIME_UTC)


SELECT CAST(CAST(Event_Date AS VARCHAR(20)) + ' ' + CAST(Hrs AS CHAR(2)) + ':' + CAST(Mins AS CHAR(2)) + ':' + CAST(Secs AS CHAR(2)) AS DATETIME) AS Event_Date_Time,  
	Total_Entries
FROM #Temp
ORDER by 1


/********************************************************

  USING THE SAME DATA SET, GET:
  -MAX AND MIN ENTRIES PER HOUR, 
  -MAX AND MIN ENTRIES PER MINUTE
  -MAX AND MIN ENTRIES PER SECOND 
  FOR EACH DAY IN THE SPECIFIED TIMEFRAME

*********************************************************/

IF OBJECT_ID('tempdb..#TotalsPerHour') IS NOT NULL
	DROP TABLE #TotalsPerHour
IF OBJECT_ID('tempdb..#TotalsPerMinute') IS NOT NULL
	DROP TABLE #TotalsPerMinute
IF OBJECT_ID('tempdb..#TotalsPerSecond') IS NOT NULL
	DROP TABLE #TotalsPerSecond

SELECT Event_Date, MAX(A.TotalEntries) AS Max_Per_Hour, MIN(A.TotalEntries) AS Min_Per_Hour
INTO #TotalsPerHour
FROM
	(SELECT Event_Date, Hrs, SUM(Total_Entries) AS TotalEntries
	 FROM #Temp
	 GROUP BY Event_Date, Hrs) AS A
GROUP BY Event_Date

SELECT Event_Date, MAX(A.TotalEntries) AS Max_Per_Minute, MIN(A.TotalEntries) AS Min_Per_Minute
INTO #TotalsPerMinute
FROM
	(SELECT Event_Date, Hrs, Mins, SUM(Total_Entries) AS TotalEntries
	 FROM #Temp
	 GROUP BY Event_Date, Hrs, Mins) AS A
GROUP BY Event_Date

SELECT Event_Date, MAX(A.TotalEntries) AS Max_Per_Second, MIN(A.TotalEntries) AS Min_Per_Second
INTO #TotalsPerSecond
FROM
	(SELECT Event_Date, Hrs, Mins, Secs, SUM(Total_Entries) as TotalEntries
	 FROM #Temp
	 GROUP BY Event_Date, Hrs, Mins, Secs) as A
GROUP BY Event_Date


SELECT DISTINCT t.Event_Date, th.Max_Per_Hour, th.Min_Per_Hour, tm.Max_Per_Minute, tm.Min_Per_Minute, ts.Max_Per_Second, ts.Min_Per_Second
FROM #Temp t
LEFT OUTER JOIN #TotalsPerHour th on t.Event_Date = th.Event_Date
LEFT OUTER JOIN #TotalsPerMinute tm on t.Event_Date = tm.Event_Date
LEFT OUTER JOIN #TotalsPerSecond ts on t.Event_Date = ts.Event_Date
ORDER BY t.Event_Date

DROP TABLE #Temp, #TotalsPerHour, #TotalsPerMinute, #TotalsPerSecond
  

/********************************************************

  LIST OF EVENT TYPES

*********************************************************/
SELECT [EVTYPEID] ,[EVTDESCR]
FROM [AccessControl].[dbo].[EVENTYPE]
ORDER BY [EVTYPEID]


/********************************************************

  COUNT OF ALL ACTIVE BADGES IN THE SYSTEM

*********************************************************/
SELECT 
	(SELECT COUNT(*) FROM [AccessControl].[dbo].[EMP]) as Total_Employee_Records,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' THEN 1 ELSE 0 END) AS Total_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' THEN 1 ELSE 0 END) AS Total_Visitor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' AND UPPER(s.Name) = 'ACTIVE' THEN 1 ELSE 0 END) AS Active_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' AND UPPER(s.Name) = 'ACTIVE'  THEN 1 ELSE 0 END) AS Active_Visitor_Badges
FROM [AccessControl].[dbo].[EMP] e
INNER JOIN [AccessControl].[dbo].[BADGE] b ON e.ID = b.EMPID
INNER JOIN [AccessControl].[dbo].[BADGETYP] bt ON b.[TYPE] = bt.ID
INNER JOIN [AccessControl].[dbo].[BADGSTAT] s ON b.[STATUS] = s.ID 


/********************************************************

  COUNT OF PANELS

*********************************************************/
SELECT COUNT(*) AS Panel_Count
FROM [AccessControl].[dbo].[ACCESSPANE]



/********************************************************

  COUNT OF READERS

*********************************************************/
SELECT COUNT(*) as Total_Readers,
SUM(PAIRMASTER) AS Total_Master,
SUM(PAIRSLAVE) AS Total_Slave,
SUM(CASE WHEN PAIRMASTER = 0 AND PAIRSLAVE = 0 THEN 1 ELSE 0 END) AS Total_Stand_Alone
FROM [AccessControl].[dbo].[Reader]



/********************************************************

  COUNT OF OUTPUTS

*********************************************************/
SELECT COUNT(*) AS Output_Pin_Count
FROM [AccessControl].[dbo].[RELAYOUTPT]


/********************************************************

  COUNT OF INPUTS

*********************************************************/
SELECT COUNT(*) AS Input_Pin_Count
FROM [AccessControl].[dbo].[ALARMINPUT]


/********************************************************

  GETS ALL EVENTS THAT OCCURRED AFTER SPECIFIED DATE/TIME.

*********************************************************/
SELECT EVENT_TIME_UTC, EVENTTYPE, ET.EVTDESCR
FROM [AccessControl].[dbo].[EVENTS] E
INNER JOIN [AccessControl].[dbo].[EVENTYPE] ET ON  E.EVENTTYPE = ET.EVTYPEID
WHERE EVENT_TIME_UTC > '2020-06-01 00:00:00.000'


/********************************************************

  TOTAL NUMBER OF EVENTS

*********************************************************/
SELECT COUNT(*) AS Total_Events
FROM [AccessControl].[dbo].[EVENTS] E


/********************************************************

  NUMBER OF EVENTS BY TYPE

*********************************************************/
select ET.EVTDESCR AS Event_Type, count(*) AS Event_Count
FROM [AccessControl].[dbo].[EVENTS] E
INNER JOIN [AccessControl].[dbo].[EVENTYPE] ET ON  E.EVENTTYPE = ET.EVTYPEID
GROUP BY ET.EVTDESCR


/********************************************************

  COUNT OF EMPLOYEES THAT HAVE A PHOTO IMAGE OR THUMBNAIL

*********************************************************/
SELECT COUNT(DISTINCT E.ID) AS Employees_With_Photo
  FROM [AccessControl].[dbo].[EMP] E
  INNER JOIN [AccessControl].[dbo].[MMOBJS] M ON E.ID = M.EMPID
  WHERE ([TYPE] = 0 AND [OBJECT] = 1) --Photo
  OR ([TYPE]  = 2 AND [OBJECT] = 1) --Thumbnail